package com.jinher.lib
{
	import com.jinher.lib.Const.AppConst;
	import com.jinher.lib.Const.LnConst;
	import com.jinher.lib.Module.SqlData;
	
	import flash.data.SQLConnection;
	import flash.data.SQLMode;
	import flash.data.SQLResult;
	import flash.data.SQLStatement;
	import flash.events.SQLErrorEvent;
	import flash.events.SQLEvent;
	import flash.filesystem.File;
	import flash.system.System;

	/**
	 * 创建人：王曙光
	 * 创建时间：2011-10-17
	 * 功能描述：数据库访问
	 */
	public final class SqlHelper
	{
		//数据库连接		
		private var conn:SQLConnection;
		//
		private var stmt:SQLStatement;
		//返回数据结构
		private var sqlData:SqlData;

		//sqlite数据库初始化
		private function initDB():void
		{
			//读取数据库文件
			var dbfile:File=File.applicationDirectory.resolvePath(AppConst.DbPath);
			//创建连接
			conn=new SQLConnection();
			//添加监听
			conn.addEventListener(SQLEvent.OPEN, openHandler);
			conn.addEventListener(SQLErrorEvent.ERROR, errorHandler);
			//打开连接
			conn.open(dbfile, SQLMode.CREATE);
			//数据返回集合
			this.sqlData=new SqlData();
		}

		private function disposeDB():void
		{
			//销毁stmt
			stmt.removeEventListener(SQLEvent.RESULT, doStmtResult);
			stmt.removeEventListener(SQLErrorEvent.ERROR, errorHandler);
			stmt.sqlConnection=null;
			stmt.text="";
			stmt=null;
			//销毁数据库连接		
			conn.removeEventListener(SQLEvent.OPEN, openHandler);
			conn.removeEventListener(SQLErrorEvent.ERROR, errorHandler);
			conn.close();
			conn=null;
			//通知gc回收无用内存
			flash.system.System.gc();
		}

		//数据库连接打开成功
		private function openHandler(event:SQLEvent):void
		{
			stmt=new SQLStatement();
			stmt.addEventListener(SQLErrorEvent.ERROR, errorHandler);
			stmt.sqlConnection=conn;
			stmt.text="";
			stmt.clearParameters();
			stmt.addEventListener(SQLEvent.RESULT, doStmtResult);
		}

		//数据库连接打开失败
		private function errorHandler(event:SQLErrorEvent):void
		{
			stmt.removeEventListener(SQLEvent.RESULT, doStmtResult);
			stmt.removeEventListener(SQLErrorEvent.ERROR, errorHandler);
			stmt.sqlConnection=null;
			stmt.text=null;
			stmt=null;
			this.sqlData=new SqlData();
			this.sqlData.Error=true;
			this.sqlData.ErrorMessage=LnConst.DbError;
			this.sqlData.DataTable=null;
		}

		//返回查询结果
		private function doStmtResult(event:SQLEvent):void
		{
			var result:SQLResult=stmt.getResult();
			this.sqlData.RowNum+=result.rowsAffected;
			this.sqlData.Error=false;
			this.sqlData.ErrorMessage="";
			if (result.data)
			{
				var numRows:int=result.data.length;
				var obj:Object;
				for (var i:int=0; i < numRows; i++)
				{
					obj=new Object();
					for (var columnName:String in result.data[i])
					{
						obj[columnName]=result.data[i][columnName];
					}
					this.sqlData.DataTable[i]=obj;
				}
			}
			else
			{
				this.sqlData.DataTable=null;
			}
		}

		public function SqlHelper()
		{

		}

		/**
		 * 执行单条SQL语句
		 * @param sql sql语句
		 * @return SqlData数据集
		 */
		public function execSql(sql:String):SqlData
		{
			initDB();
			stmt.text=sql;
			stmt.execute();
			disposeDB();
			return this.sqlData;
		}

		/**
		 * 执行单条SQL语句，附带有参数
		 * @param sql sql语句
		 * @param params 参数集合
		 * @return SqlData数据集
		 */
		public function execSqlWithParams(sql:String, params:Array):SqlData
		{
			this.initDB();
			this.stmt.text=sql;
			for (var i:int=0; i < params.length; i++)
			{
				this.stmt.parameters[":" + params[i].key]=params[i].value;
			}
			this.stmt.execute();
			disposeDB();
			return this.sqlData;
		}

		/**
		 * 开启一个事务
		 */
		public function BegionTrans():void
		{
			this.initDB();
			conn.begin();
		}

		/**
		 * 作为事务执行 
		 */		
		public function execSqlofTrans(sql:String, params:Array):void
		{
			this.stmt.text=sql;
			for (var i:int=0; i < params.length; i++)
			{
				this.stmt.parameters[":" + params[i].key]=params[i].value;
			}
			this.stmt.execute();
		}

		/**
		 * 提交事务，返回数据 
		 */		
		public function CommitTrans():SqlData
		{
			conn.commit();
			this.disposeDB();
			return this.sqlData;
		}

		/**
		 * 通过事物执行SQL语句，提升效率
		 * @param sql sql语句
		 * @param params 参数集合
		 * @param data 数据集合
		 * @return SqlData数据集
		 */
		public function execTrans(sql:String, params:Array, data:Array):SqlData
		{
			this.initDB();
			//开始事物
			conn.begin();
			stmt.text=sql;
			for (var i:int=0; i < data.length; i++)
			{
				stmt.clearParameters();
				for (var j:int=0; j < params.length; j++)
				{
					stmt.parameters[":" + params[j]]=data[i][params[j]];
				}
				stmt.execute();
			}
			//提交事务
			conn.commit()
			disposeDB();
			return this.sqlData;
		}
	}
}
